using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DataTransferObject;

namespace DataAccess
{
    public class clsPMuonSachDAO:clsDataAccessHelper
    {
         // Fields
        private String tenTable;

        // Contructors
        public clsPMuonSachDAO()
        {
            this.tenTable = "PMUONSACH";
        }
        #region Methods

        //Lấy tiền đặt cọc của phiếu mượn.
        public long LayTienDatCocCuaPhieuMuon(String MaPhieuMuon)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT TIENDATCOC";
                sql += " FROM PMUONSACH P";
                sql += " WHERE MAPHIEUMUON LIKE '" + MaPhieuMuon + "'";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return Convert.ToInt64(dt.Rows[0].ItemArray[0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Lấy tiền nợ của DKCB thuộc phiếu mượn.
        public long LayTienNoCuaDKCB_PhieuMuon(String DKCB, String MaPhieuMuon)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT TIENNO";
                sql += " FROM PMUONSACH P, CTMUONTRA C";
                sql += " WHERE C.DKCB LIKE '" + DKCB + "'";
                sql += " AND P.MAPHIEUMUON LIKE '" + MaPhieuMuon + "'";
                sql += " AND TRANGTHAIMT LIKE N'Đang Mượn'";
                sql += " AND P.MAPHIEUMUON=C.MAPHIEUMUON";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return Convert.ToInt64(dt.Rows[0].ItemArray[0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Lấy tiền nợ của tất cả các phiếu độc giả đã và đang mượn.
        public long LayTienNoTatCaPhieuMuonSach(String MaThe)
        {
            String sql;
            long TienNoPhieuMuon;
            try
            {
                sql = "SELECT SUM(TIENNO)";
                sql += " FROM PMUONSACH P";
                sql += " WHERE P.MATHE LIKE '" + MaThe + "'";
                sql += " GROUP BY MATHE";
                TienNoPhieuMuon = this.ExecuteScalar(sql);

                return TienNoPhieuMuon;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public long LayTienNoTrongPhieuMuonSachCuaDocGia(String MaThe, String MaPhieuMuon)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT TIENNO";
                sql += " FROM PMUONSACH P";
                sql += " WHERE P.MATHE LIKE '" + MaThe + "' AND MAPHIEUMUON LIKE '" + MaPhieuMuon + "'";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return Convert.ToInt64(dt.Rows[0].ItemArray[0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public long LayTienPhatCuaDKCB(String DKCB)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT C.TIENPHAT";
                sql += " FROM PMUONSACH P, CTMUONTRA C";
                sql += " WHERE C.DKCB LIKE '" + DKCB + "'";
                sql += " AND P.MAPHIEUMUON=C.MAPHIEUMUON";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return Convert.ToInt64(dt.Rows[0].ItemArray[0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public String TimMaPhieuMuonTheoMaThe_DKCB(String MaThe, String DKCB)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT P.MAPHIEUMUON";
                sql += " FROM PMUONSACH P, CTMUONTRA C";
                sql += " WHERE C.DKCB LIKE '" + DKCB + "'";
                sql += " AND P.MATHE LIKE '" + MaThe + "'";
                sql += " AND TRANGTHAIMT LIKE N'Đang Mượn'";
                sql += " AND P.MAPHIEUMUON=C.MAPHIEUMUON";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return dt.Rows[0].ItemArray[0].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public String TimMaPhieuMuonTheoDKCB(String DKCB)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT P.MAPHIEUMUON";
                sql += " FROM PMUONSACH P, CTMUONTRA C";
                sql += " WHERE C.DKCB LIKE '" + DKCB + "'";
                sql += " AND P.MAPHIEUMUON=C.MAPHIEUMUON";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return dt.Rows[0].ItemArray[0].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateTienNo(clsPMuonSachDTO phieumuonsach)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy UPDATE " + this.tenTable;
                sql += " SET";
                sql += " TIENNO = '";
                sql += phieumuonsach.TienNo + "',";
                sql += " TIENDATCOC = '";
                sql += phieumuonsach.TienDatCoc + "'";
                sql += " WHERE MAPHIEUMUON LIKE '";
                sql += phieumuonsach.MaPhieuMuon + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }

        public DataTable LayDanhSachSachDocGiaDangMuon(clsDocGiaDTO DocGiaDTO)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT P.MAPHIEUMUON, P.MATHE, NGAYMUON, TIENNO, TIENDATCOC, NGAYHETHANS, C.DKCB, NGAYTRA, TIENPHAT, SONGAYMUON, TRANGTHAIMT, T.TENTUASACH, GIA";
                sql += " FROM PMUONSACH P, CTMUONTRA C, TUASACH T, SACH S";
                sql += " WHERE P.MATHE LIKE '" + DocGiaDTO.MaThe + "'";
                sql += " AND TRANGTHAIMT LIKE N'Đang Mượn'";
                sql += " AND P.MAPHIEUMUON=C.MAPHIEUMUON";
                sql += " AND T.MATUASACH=S.MATUASACH";
                sql += " AND S.DKCB=C.DKCB";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //-------------------------------------Lấy dữ liệu bảng Phiếu Mượn Sách-----------------------



        public DataTable SelectAll()
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT * FROM " + this.tenTable;
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //-----------------------------------------------------------------------------//

        //-------------------------------------Thêm vào bảng Phiếu Mượn Sách-----------------------

        public bool Insert(clsPMuonSachDTO phieumuonsach)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy INSERT INTO " + this.tenTable;
                sql += " values('";
                sql += phieumuonsach.MaPhieuMuon + "','";
                sql += phieumuonsach.MaThe + "','";
                sql += phieumuonsach.NgayMuon.Day + "/" + phieumuonsach.NgayMuon.Month + "/" + phieumuonsach.NgayMuon.Year + "','";
                sql += phieumuonsach.TienNo + "','";
                sql += phieumuonsach.TienDatCoc + "','";
                sql += phieumuonsach.NgayHetHan.Day + "/" + phieumuonsach.NgayHetHan.Month + "/" + phieumuonsach.NgayHetHan.Year + "')";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }

        //-----------------------------------------------------------------------------//


        //-------------------------------------Xóa vào bảng Phiếu Mượn Sách-----------------------
        public bool Delete(clsPMuonSachDTO phieumuonsach)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "DELETE ";
                sql += " FROM " + this.tenTable;
                sql += " WHERE MAPHIEUMUON LIKE '" + phieumuonsach.MaPhieuMuon + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }
        //-----------------------------------------------------------------------------//


        //-------------------------------------Sửa vào bảng Phiếu Mượn Sách-----------------------

        public bool Update(clsPMuonSachDTO phieumuonsach)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy UPDATE " + this.tenTable;
                sql += " SET";
                sql += " MATHE = '";
                sql += phieumuonsach.MaThe + "',";
                sql += " NGAYMUON = '";
                sql += phieumuonsach.NgayMuon + "',";
                sql += " TIENNO = '";
                sql += phieumuonsach.TienNo + "',";
                sql += " TIENDATCOC = '";
                sql += phieumuonsach.TienDatCoc + "',";
                sql += " NGAYHETHANS = '";
                sql += phieumuonsach.NgayHetHan + "'";
                sql += " WHERE MAPHIEUMUON LIKE '";
                sql += phieumuonsach.MaPhieuMuon + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }
        //-----------------------------------------------------------------------------//

        //-------------------------------------Sửa vào Ngày Trả của Phiếu Mượn Sách-----------------------

        public bool SuaNgayTraSach(clsPMuonSachDTO phieumuonsach)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy UPDATE " + this.tenTable;
                sql += " SET";
                sql += " NGAYHETHANS ='";
                sql += phieumuonsach.NgayHetHan.Day + "/" + phieumuonsach.NgayHetHan.Month + "/" + phieumuonsach.NgayHetHan.Year + "'";
                sql += " WHERE MAPHIEUMUON like '";
                sql += phieumuonsach.MaPhieuMuon + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }
        //-----------------------------------------------------------------------------//

        #endregion

    }
}
